using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SQLite;

namespace LeisureCenter.Core
{
    public class InvalidDatabaseException : Exception
    {
    }

    public class DatabaseManager
    {
        private const int DATABASE_VERSION = 11;

        public DatabaseManager()
        {
            string database = String.Format(@"{0}\LeisureCenter.db", LeisureCenterInstance.DataPath);
            Properties.Settings.Default["ConnectionString"] = String.Format(@"data source={0}", database);
            if (!File.Exists(database))
                CreateDatabase();
            else
                UpdateDatabaseIfNeeded(database);
        }

        public static int ExecuteNonQuery(string query)
        {
            SQLiteConnection connection = new SQLiteConnection(Properties.Settings.Default.ConnectionString);
            connection.Open();
            try
            {
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = query;
                return command.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }

        public static Object ExecuteScalar(string query)
        {
            SQLiteConnection connection = new SQLiteConnection(Properties.Settings.Default.ConnectionString);
            connection.Open();
            try
            {
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = query;
                return command.ExecuteScalar();
            }
            finally
            {
                connection.Close();
            }
        }

        public static string ExecuteStringScalar(string query, string def)
        {
            Object result = ExecuteScalar(query);
            if (result == null || result == DBNull.Value || !(result is string))
                return def;
            else
                return (string)result;
        }

        public static long ExecuteLongScalar(string query, long def)
        {
            Object result = ExecuteScalar(query);
            if (result == null || result == DBNull.Value || !(result is long))
                return def;
            else
                return (long)result;
        }

        public static IEnumerable<string> ExecuteStringsQuery(string query)
        {
            SQLiteConnection connection = new SQLiteConnection(Properties.Settings.Default.ConnectionString);
            connection.Open();
            try
            {
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = query;
                SQLiteDataReader reader = command.ExecuteReader();
                List<string> result = new List<string>();
                while (reader.Read())
                    result.Add(reader.GetString(0));
                return result;
            }
            finally
            {
                connection.Close();
            }
        }

        public static IEnumerable<long> ExecuteLongsQuery(string query)
        {
            SQLiteConnection connection = new SQLiteConnection(Properties.Settings.Default.ConnectionString);
            connection.Open();
            try
            {
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = query;
                SQLiteDataReader reader = command.ExecuteReader();
                List<long> result = new List<long>();
                while (reader.Read())
                    result.Add(reader.GetInt64(0));
                return result;
            }
            finally
            {
                connection.Close();
            }
        }

        public static IEnumerable<KeyValuePair<string, long>> ExecuteStringsLongsQuery(string query)
        {
            SQLiteConnection connection = new SQLiteConnection(Properties.Settings.Default.ConnectionString);
            connection.Open();
            try
            {
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = query;
                SQLiteDataReader reader = command.ExecuteReader();
                Dictionary<string, long> result = new Dictionary<string, long>();
                while (reader.Read())
                    result.Add(reader.GetString(0), reader.GetInt64(1));
                return result;
            }
            finally
            {
                connection.Close();
            }
        }

        private static void UpdateDatabaseIfNeeded(string database)
        {
            SQLiteConnection connection = new SQLiteConnection(Properties.Settings.Default.ConnectionString);
            connection.Open();
            try
            {
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = @"select value from Settings where name = 'Version'";
                string version = (string)command.ExecuteScalar();
                if (version == null)
                {
                    throw new InvalidDatabaseException();
                }
                else
                {
                    int ver = int.Parse(version);
                    if (ver > DATABASE_VERSION || ver < 1)
                        throw new InvalidDatabaseException();
                    else
                        if (ver < DATABASE_VERSION)
                        {
                            string backup = database + "." + ver;
                            while (File.Exists(backup))
                                backup += '_';
                            File.Copy(database, backup);
                            for (int i = ver; i < DATABASE_VERSION; i++)
                            {
                                command = connection.CreateCommand();
                                command.CommandText = Properties.Resources.ResourceManager.GetString(string.Format(@"UpdateTables_{0}_{1}_sql", i, i + 1));
                                command.ExecuteNonQuery();
                            }
                        }
                }
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateDatabase()
        {
            ExecuteNonQuery(Properties.Resources.CreateTables_sql);
        }
    }
}
